package cn.javaplus.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class Run {
	
//	CREATE TABLE `logs` (
//			  `log_id` int(11) NOT NULL AUTO_INCREMENT,
//			  `uname` varchar(32) NOT NULL,
//			  `create_time` date NOT NULL COMMENT '日志生成时间',
//			  `type` int(11) NOT NULL COMMENT '日志类别',
//			  `log` varchar(1024) NOT NULL COMMENT '日志记录',
//			  PRIMARY KEY (`log_id`)
//			) ENGINE=InnoDB AUTO_INCREMENT=2011 DEFAULT CHARSET=utf8;


	public static void main(String[] args) {
		try { // load the driver 
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance(); 
            System.out.println("Load the embedded driver"); 
            Connection conn = null; 
           //create and connect the database named helloDB  
            conn=DriverManager.getConnection("jdbc:derby:helloDB;create=true"); 
            System.out.println("create and connect to helloDB"); 
            conn.setAutoCommit(false); 
 
            // create a table and insert two records 
            Statement s = conn.createStatement(); 
            s.execute("create table logs(uname varchar(40), score int, primary key(uname))"); 
            System.out.println("Created table logs"); 
            s.execute("insert into logs values('Ruth Cao', 86)"); 
            s.execute("insert into logs values ('Flora Shi', 92)"); 
            // list the two records 
            ResultSet rs = s.executeQuery( 
                "SELECT name, score FROM logs ORDER BY score"); 
            System.out.println("name\t\tscore"); 
            while(rs.next()) { 
                StringBuilder builder = new StringBuilder(rs.getString(1)); 
                builder.append("\t"); 
                builder.append(rs.getInt(2)); 
                System.out.println(builder.toString()); 
            } 
            // delete the table 
            s.execute("drop table logs"); 
            System.out.println("Dropped table logs"); 
             
            rs.close(); 
            s.close(); 
            System.out.println("Closed result set and statement"); 
            conn.commit(); 
            conn.close(); 
            System.out.println("Committed transaction and closed connection"); 
             
            try { // perform a clean shutdown  
                DriverManager.getConnection("jdbc:derby:;shutdown=true"); 
            } catch (SQLException se) { 
                System.out.println("Database shut down normally"); 
            } 
        } catch (Throwable e) { 
           e.printStackTrace();
        } 
		System.out.println("SimpleApp finished");
	}
}
